import logging
from typing import Optional

from fastapi import APIRouter, Query, Path
from fastapi.params import Depends
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

supplier_router = APIRouter(prefix="/purchase/tsSupplier", tags=["供应商资料管理"],
                            dependencies=[Depends(oauth2_scheme)])


class SupplierSearch(PaginationBase):
    fsiteNo: str =Field(None,description="客户编号")
    fsupname: str =Field(None,description="供应商名称")
    flinkman: str =Field(None,description="")
    ftype: str =Field(None,description="供应商类型")


@supplier_router.get("/list")
def list_suppliers(supplier: SupplierSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_ts_supplier where 1=1"
    if supplier.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = supplier.fsiteNo
    if supplier.fsupname is not None:
        sql = sql + " and fsupname like concat('%', :fsupname ,'%')"
        param["fsupname"] = supplier.fsupname
    if supplier.ftype is not None:
        sql = sql + " and ftype = :ftype "
        param["ftype"] = supplier.ftype
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsupno,fsupname,fsite_no,faccname,faccno,faccbank,"
    sql = sql + " fcorptaxno,fofficeaddress,fprovince,fcity,ftype,fsupkind,"
    sql = sql + " fstate,fupddate,foperator"
    sql = sql + " from s_ts_supplier where 1=1"
    if supplier.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = supplier.fsiteNo
    if supplier.fsupname is not None:
        sql = sql + " and fsupname like concat('%', :fsupname ,'%')"
        param["fsupname"] = supplier.fsupname
    if supplier.ftype is not None:
        sql = sql + " and ftype = :ftype "
        param["ftype"] = supplier.ftype
    if supplier.order == "desc":
        sql = sql + " order by  fsupno desc "
    else:
        sql = sql + " order by  fsupno  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = supplier.limit
    param["offset"] = supplier.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fupddate = ""
        try:
            fupddate = data[key][13].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        d1 = {}
        d1['fsupno'] = data[key][0]
        d1['fsupname'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['faccname'] = data[key][3]
        d1['faccno'] = data[key][4]
        d1['faccbank'] = data[key][5]
        d1['fcorptaxno'] = data[key][6]
        d1['fofficeaddress'] = data[key][7]
        d1['fprovince'] = data[key][8]
        d1['fcity'] = data[key][9]
        d1['ftype'] = data[key][10]
        d1['fsupkind'] = data[key][11]
        d1['fstate'] = data[key][12]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][14]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": supplier.limit, "page": supplier.pageIndex,
                 "pages": (count + supplier.limit - 1) // supplier.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class SupplierCreate(BaseModel):
    """供应商创建模型"""
    fsupno: str = Field(..., description="供应商编号", min_length=1, max_length=20)
    fsupname: str = Field(..., description="供应商名称", min_length=1, max_length=100)
    fsiteNo: str = Field(..., description="站点编号", min_length=1, max_length=20)
    ftype: int = Field(..., description="供应商类型", ge=0)  # 类型为非负整数
    fsupkind: int = Field(..., description="供应商种类", ge=0)  # 种类为非负整数
    fstate: int = Field(1, description="状态", ge=0)  # 默认状态为1（启用）
    foperator: str = Field(..., description="操作人", min_length=1)  # 操作人不能为空

    # 可选字段（带有默认空字符串）
    flinkman: str = Field("", description="联系人")
    fphone: str = Field("", description="手机", max_length=20)
    ftelno: str = Field("", description="电话", max_length=20)
    femail: str = Field("", description="邮箱", max_length=100)
    fofficeaddress: str = Field("", description="办公地址", max_length=200)
    fprovince: str = Field("", description="省份", max_length=20)
    fcity: str = Field("", description="城市", max_length=20)
    faccname: str = Field("", description="账户名称", max_length=100)
    faccno: str = Field("", description="银行账号", max_length=30)
    faccbank: str = Field("", description="开户银行", max_length=100)
    fcorptaxno: str = Field("", description="税号", max_length=30)
    fupddate: Optional[str] = Field(None, description="更新日期")  # 可选日期字符串


@supplier_router.post("/create", summary="新增供应商信息")
def create_supplier(supplier: SupplierCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fsupno from s_ts_supplier where fsupno= :fsupno "
            data = db.execute(text(sql), {"fsupno": supplier.fsupno}).fetchall()
            if data is not None and len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into s_ts_supplier(fsupno,fsupname,fsite_no,"
                sql = sql + "flinkman,fphone,ftelno,femail,fofficeaddress,fprovince,fcity,fcorptaxno,faccbank,faccno,faccname,ftype,fsupkind,fstate,fupddate,foperator)"
                sql = sql + (
                    " values( :fsupno , :fsupname , :fsiteNo , :flinkman , :fphone , :ftelno , :femail , :fofficeaddress ,"
                    " :fprovince , :fcity , :fcorptaxno , :faccbank , :faccno , :faccname , :ftype , :fsupkind , :fstate ,"
                    " now(),:foperator);")
                db.execute(text(sql), {
                    "fsupno": supplier.fsupno,
                    "fsupname": supplier.fsupname,
                    "fsiteNo": supplier.fsiteNo,
                    "flinkman": supplier.flinkman,
                    "fphone": supplier.fphone,
                    "ftelno": supplier.ftelno,
                    "femail": supplier.femail,
                    "fofficeaddress": supplier.fofficeaddress,
                    "fprovince": supplier.fprovince,
                    "fcity": supplier.fcity,
                    "fcorptaxno": supplier.fcorptaxno,
                    "faccbank": supplier.faccbank,
                    "faccno": supplier.faccno,
                    "faccname": supplier.faccname,
                    "ftype": supplier.ftype,
                    "fsupkind": supplier.fsupkind,
                    "fstate": supplier.fstate,
                    "foperator": supplier.foperator
                })

                # 返回
                d1 = {}
                d1['fsupno'] = supplier.fsupno
                d1['fsupname'] = supplier.fsupname
                d1['fsiteno'] = supplier.fsiteNo
                d1['flinkman'] = supplier.flinkman
                d1['fphone'] = supplier.fphone
                d1['ftelno'] = supplier.ftelno
                d1['femail'] = supplier.femail
                d1['faccname'] = supplier.faccname
                d1['faccno'] = supplier.faccno
                d1['faccbank'] = supplier.faccbank
                d1['fcorptaxno'] = supplier.fcorptaxno
                d1['fofficeaddress'] = supplier.fofficeaddress
                d1['fprovince'] = supplier.fprovince
                d1['fcity'] = supplier.fcity
                d1['ftype'] = supplier.ftype
                d1['fsupkind'] = supplier.fsupkind
                d1['fstate'] = supplier.fstate
                d1['fupddate'] = supplier.fupddate
                d1['foperator'] = supplier.foperator
                return BaseResponse.success(data=d1)

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@supplier_router.post("/update", summary="修改供应商接口")
def update_supplier(supplier: SupplierCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update s_ts_supplier"
            sql = sql + " set fsupname= :fsupname ,flinkman= :flinkman "
            sql = sql + ",fsupkind= :fsupkind ,ftype= :ftype "
            sql = sql + ",fphone= :fphone ,ftelno= :ftelno ,femail= :femail "
            sql = sql + ",fofficeaddress= :fofficeaddress ,fprovince= :fprovince "
            sql = sql + ",fcity= :fcity ,fstate= :fstate "
            sql = sql + ",fcorptaxno= :fcorptaxno ,faccbank= :faccbank "
            sql = sql + ",faccno= :faccno ,faccname= :faccname "
            sql = sql + ",fupddate= :fupddate ,foperator= :userid "
            sql = sql + " where fsupno= :fsupno  and fsite_no :fsiteNo "
            db.execute(text(sql), {
                "fsupname": supplier.fsupname,
                "flinkman": supplier.flinkman,
                "fsupkind": supplier.fsupkind,
                "ftype": supplier.ftype,
                "fphone": supplier.fphone,
                "ftelno": supplier.ftelno,
                "femail": supplier.femail,
                "fofficeaddress": supplier.fofficeaddress,
                "fprovince": supplier.fprovince,
                "fcity": supplier.fcity,
                "fstate": supplier.fstate,
                "fcorptaxno": supplier.fcorptaxno,
                "faccbank": supplier.faccbank,
                "faccno": supplier.faccno,
                "faccname": supplier.faccname,
                "fupddate": supplier.fupddate,
                "userid": user_id,
                "fsupno": supplier.fsupno,
                "fsiteNo": supplier.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@supplier_router.post("/delete/{fsupno}", summary="删除供应商信息")
def delete_supplier(fsupno: str = Path(description="供应商编号"), db: Session = Depends(get_db)):
    sql = "delete from s_ts_supplier where fsupno= :fsupno "
    db.execute(text(sql), {
        "fsupno": fsupno
    })
    return BaseResponse.success()
